package com.QK.cnstudy.db;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.QK.cnstudy.entity.Author;
import com.QK.cnstudy.entity.Sentence;
import com.QK.cnstudy.entity.Word;
import com.QK.cnstudy.entity.Work;
import com.QK.cnstudy.entity.WorkRecord;

public class DBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "cn_study_db";
    private static final int DATABASE_VERSION = 3;
    Context context;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        execSqlFileMultiLine(db, "create_table.sql");
        // insertData(db);
        insertData2(db);
        initTable(db);
    }

    public void initTable(SQLiteDatabase db) {
        db.execSQL("insert into cur_study_info values(-1)");
        db.execSQL("insert into stat values (0,0,0,0,0,0,0,0)");
    }

    public void insertData2(SQLiteDatabase db) {
        insertTextbook(db);
        insertTerms(db);

        loadWorkFromJson(db, "work_people.txt");
        loadSentencesFromJson(db, "work1.txt");
        loadWordsFromJson(db, "work1.txt");
        // insertTestData(db);

    }

    public void loadSentencesFromJson(SQLiteDatabase db, String filePath) {
        InputStream is = null;
        String jsonString = "";
        try {
            is = this.context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                jsonString += line;
            }
            List<Sentence> sentences = Work.getSentencesFromJson(jsonString);

            for (Sentence sentence : sentences) {
                sentence.saveToDB(db);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void loadWordsFromJson(SQLiteDatabase db, String filePath) {
        InputStream is = null;
        String jsonString = "";
        try {
            is = this.context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                jsonString += line;
            }
            List<Word> words = Work.getWordFromJson(jsonString);

            for (Word word : words) {
                word.saveToDB(db);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void loadWorkFromJson(SQLiteDatabase db, String filePath) {
        InputStream is = null;
        String jsonString = "";
        try {
            is = this.context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                jsonString += line;
            }
            List<Work> words = Work.getWorksFromJson(jsonString);

            for (Work work : words) {
                if (work.getAuthor().isExist(db) == false)
                    work.getAuthor().saveToDB(db);
                work.saveToDB(db);
            }
            Log.v("load work", "load " + words.size() + "\n");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void insertTestData(SQLiteDatabase db) {

    }

    public void insertTerms(SQLiteDatabase db) {
        db.execSQL("insert into term values(?,?)", new Object[] { 1, "七年级上" });
        db.execSQL("insert into term values(?,?)", new Object[] { 2, "七年级下" });
        db.execSQL("insert into term values(?,?)", new Object[] { 3, "八年级上" });
        db.execSQL("insert into term values(?,?)", new Object[] { 4, "八年级下" });
        db.execSQL("insert into term values(?,?)", new Object[] { 5, "九年级上" });
        db.execSQL("insert into term values(?,?)", new Object[] { 6, "九年级下" });
    }

    public void insertTextbook(SQLiteDatabase db) {
        db.execSQL("insert into textbook values(?,?)", new Object[] { 1, "人教版 2013" });
        db.execSQL("insert into textbook values(?,?)", new Object[] { 2, "苏教版 2013" });
    }

    public void insertData(SQLiteDatabase db) {
        // loadWork(db, "work.txt");
        // loadSentence(db, "sentence.txt");
        // loadWord(db, "word.txt");
        loadAuthor(db, "author.txt");
        loadWorkAndSentenceAndWord(db, "work_sentence.txt", "work_word.txt");
    }

    public void loadWorkAndWord(SQLiteDatabase db, String filePath) {

    }

    public void loadWorkAndSentenceAndWord(SQLiteDatabase db, String file1, String file2) {
//        int workId = 1, sentenceId = 1, wordId = 1;
//        String preTitle = "", title = "", subTitle = "", sentence = "", translate = "", word = "";
//        List<Work> works = new ArrayList<Work>();
//        List<Sentence> sentences = new ArrayList<Sentence>();
//        InputStream is = null;
//        try {
//            is = context.getResources().getAssets().open(file1);
//            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
//            String line = "";
//            while ((line = bufReader.readLine()) != null) {
//                String[] strings = line.split("\t");
//                title = strings[0];
//                subTitle = strings[1];
//                sentence = strings[2];
//                translate = strings[3];
//                Sentence sentenceObj = new Sentence();
//                sentenceObj.setId(sentenceId++);
//                sentenceObj.setContent(sentence);
//                sentenceObj.setTranslation(translate);
//                sentenceObj.setWordlist("");
//                sentences.add(sentenceObj);
//                if (title.length() == 0 && subTitle.length() == 0) {
//                    works.get(works.size() - 1).setSentencelist(
//                                    works.get(works.size() - 1).getSentencelist() + "," + (sentenceId - 1));
//                } else {
//                    if (title != "") {
//                        preTitle = title;
//                    } else if (title == "" && subTitle != "") {
//                        title = preTitle;
//                    }
//                    Work work = new Work();
//                    work.setId(workId++);
//                    work.setAuthor("");
//                    work.setInfo("");
//                    work.setName(title);
//                    work.setSubname(subTitle);
//                    work.setSentencelist("" + (sentenceId - 1));
//                    work.setWordlist("");
//                    works.add(work);
//                }
//            }
//        } catch (Exception e) {
//            Log.i("load", "load work and sentence failed");
//        }
//
//        workId = 0;
//        List<Word> words = new ArrayList<Word>();
//        try {
//            is = context.getResources().getAssets().open(file2);
//            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
//            String line = "";
//            while ((line = bufReader.readLine()) != null) {
//                String[] strings = line.split("\t");
//                int len = strings.length;
//                title = strings[0];
//                subTitle = strings[1];
//                word = strings[2];
//                translate = len > 3 ? strings[3] : "";
//                Word wordObj = new Word();
//                wordObj.setId(wordId++);
//                wordObj.setContent(word);
//                wordObj.setTranslation(translate);
//                wordObj.setMistakeTimes(0);
//                wordObj.setWordtype(0);
//                words.add(wordObj);
//
//                if (title.length() == 0 && subTitle.length() == 0) {
//                    works.get(workId - 1).setWordlist(works.get(workId - 1).getWordlist() + "," + (wordId - 1));
//                } else {
//                    workId++;
//                    works.get(workId - 1).setWordlist("" + (wordId - 1));
//                }
//            }
//        } catch (Exception e) {
//            Log.i("load", "load work and word failed");
//        }
//
//        for (Word wordObj : words) {
//            wordObj.saveToDB(db);
//        }
//        for (Sentence sentenceObj : sentences) {
//            sentenceObj.saveToDB(db);
//        }
//
//        List<WorkRecord> workRecords = new ArrayList<WorkRecord>();
//        for (Work work : works) {
//            work.saveToDB(db);
//
//            WorkRecord workRecord = new WorkRecord();
//            workRecord.setId(work.getId());
//
//            if (work.getSubname().compareTo("") != 0) {
//                workRecord.setWorkName(work.getSubname());
//            } else {
//                workRecord.setWorkName(work.getName());
//            }
//
//            workRecord.setLastTime(0L);
//            workRecord.setOpenTimes(0);
//            workRecords.add(workRecord);
//        }
//
//        for (WorkRecord workRecord : workRecords) {
//            workRecord.saveToDB(db);
//        }
    }

    public void loadWord(SQLiteDatabase db, String filePath) {
        List<Word> words = new ArrayList<Word>();
        InputStream is = null;
        try {
            is = context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                String[] strings = line.split("\t");
                Word word = new Word(strings);
                words.add(word);
            }
        } catch (Exception e) {
            Log.i("load", "load sentence failed");
        }
        for (Word word : words) {
            word.saveToDB(db);
        }
    }

    public void loadSentence(SQLiteDatabase db, String filePath) {
        List<Sentence> sentences = new ArrayList<Sentence>();
        InputStream is = null;
        try {
            is = context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                String[] strings = line.split("\t");
                Sentence sentence = new Sentence(strings);
                sentences.add(sentence);
            }
        } catch (Exception e) {
            Log.i("load", "load sentence failed");
        }

        for (Sentence sentence : sentences) {
            sentence.saveToDB(db);
        }
    }

    public void loadAuthor(SQLiteDatabase db, String filePath) {
        List<Author> authors = new ArrayList<Author>();
        InputStream is = null;
        try {
            is = context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                String[] strings = line.split("\t");
                Author author = new Author(strings);
                authors.add(author);
            }
        } catch (Exception e) {
            Log.i("load", "load sentence failed");
        }

        for (Author author : authors) {
            author.saveToDB(db);
        }
    }

    public void loadWork(SQLiteDatabase db, String filePath) {
        List<Work> works = new ArrayList<Work>();
        InputStream is = null;
        try {
            is = context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                String[] strings = line.split("\t");
                Work work = new Work(strings);
                works.add(work);
            }
        } catch (Exception e) {
            Log.i("load", "load work failed");
        }
        List<WorkRecord> workRecords = new ArrayList<WorkRecord>();
        for (Work work : works) {
            work.saveToDB(db);

            WorkRecord workRecord = new WorkRecord();
            workRecord.setId(work.getId());

            if (work.getSubname().compareTo("") != 0) {
                workRecord.setWorkName(work.getSubname());
            } else {
                workRecord.setWorkName(work.getName());
            }

            workRecord.setLastTime(0L);
            workRecord.setOpenTimes(0);
            workRecords.add(workRecord);
        }

        for (WorkRecord workRecord : workRecords) {
            workRecord.saveToDB(db);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        execSqlFileMultiLine(db, "create_table.sql");
        // insertData(db);
        insertData2(db);

        initTable(db);
    }

    // 单行
    public void execSqlFileSingleLine(SQLiteDatabase db, String filePath) {
        InputStream is = null;
        String sql = "";
        try {
            is = this.context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                if (line.length() != 0) {
                    db.execSQL(line);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 多行，以空行间隔
    public void execSqlFileMultiLine(SQLiteDatabase db, String filePath) {
        InputStream is = null;
        String sql = "";
        try {
            is = this.context.getResources().getAssets().open(filePath);
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(is, "utf-8"));
            String line = "";
            while ((line = bufReader.readLine()) != null) {
                if (line.length() != 0) {
                    sql += line;
                } else {
                    db.execSQL(sql);
                    sql = "";
                }
            }
            db.execSQL(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
